Feed aggregator

After the “Welcome 3rd Day APEX Connect, DOAG e.V.”, and the very entertaining Keynote “Trouble in the Old Republic” by Samuel Nitsche, I decided to attend presentations on following topics:
– 23ai – Building an AI Vector Search API using APEX, ORDS, REST and PL/SQL
– APEX in Style – Ein Überblick über die verschiedenen UI-Customizingmöglichkeiten
– SQL und PL/SQL: Tipps & Tricks für APEX Entwickler
– Oracle APEX & Entra ID: Effiziente Benutzerverwaltung mit Workflows und SSO
Beside the presentations I also had the privilege to have 1:1 sessions with Carsten Czarski, Florian Grasshoff and Mark Swetz from the APEX development Team.

23ai – Building an AI Vector Search API using APEX, ORDS, REST and PL/SQL

Vectors are lists of numbers and their dimension is given by the amount of numbers in the vector definition. The creation of a vector from any other data is called vectorizing or embedding.
Oracle 23ai is having a new vector datatype and an associated PL/SQL package DBMS_VECTOR. Pre-trained Models can be imported based on ONNX standard.
APEX can be used to call external AI models as web services. Any needed transformation can be done thanks to the DBMS_VECTOR package.
One of the main advantage of vector search is language independent.

APEX in Style – Ein Überblick über die verschiedenen UI-Customizingmöglichkeiten

New template components allow to set attributes in templates for declarative usage in APEX.
This can be combined with dedicated CSS to be used in the template component. Those component can be used in any kind of pages (e.g. Interactive Report, Card Reports, …).
When changing templates, it is recommended to do them on a copy in order to be able to rollback to the original one if needed.
Beside the templates Theme can be modified globally with Theme styles over the theme roller. Theme changes can even be allowed to end users in APEX so they can personalize the look & Feel of the application.

SQL und PL/SQL: Tipps & Tricks für APEX Entwickler

SQL queries are in the heart of APEX. Looking into the debugger details of the SQL produced by APEX can be seen. Any filtering or other change will add to the original query and generate a new query which can be seen as onion SQL with following levels adding up:
– Component SQL (written by the developer)
– LOVs
– Computed columns
– Filter
– Sorts
– Aggregation
– Pagination
This means the query run by APEX can be very different than the one entered by the developer.
As a consequence, sorting with ORDER BY should never be part of the component SQL. Use the declarative column sorting parameter instead.
APEX allows to use pseudo-hints in the declarative optimizer hints field in order manage the pagination type.
PL/SQL tips:
– functions in SELECT are run on every row selected (expensive)
– functions in WHERE are run for all rows of the selected table (even more expensive)
– use bind variables so that substitution is happening in the database
– strictly define constants
– name loops
– map parameters
– always raise in “when others” clause of exception handling
– use conditional compilation

Oracle APEX & Entra ID: Effiziente Benutzerverwaltung mit Workflows und SSO

User Management requires an IAM system for easier and centralized use. One combination for APEX is with Microsoft ENTRA.
Possible usage:
– on / offboarding
– details and contact management
APEX is managing access to ENTRA through web services which allows to easily cover the previous use cases. Web services are part of declarative setup to address the Microsoft Graph interface and manage authorizations mapped to application and delegation over groups with ENTRA.
Access is secured with oAuth authentication.

NEWS!

One last news, the support of the last 3 APEX versions (23.2, 24.1 and 24.2) might be extended to 2 years instead of 18 months.

You can find a summary of Day 2 here.

That was the final day of APEX Connect 2025 conference. Great organization, great presentations and great people.
Hope to see all again on APEX Connect 2026.
How about you? Are you planning to join?

L’article APEX Connect 2025 (Day 3) est apparu en premier sur dbi Blog.

Tom, Is there a way I can find the first day and last day of a given quarter. In a given year, there are 4 quarters. If I select first quarter then I should be able to get the first day and last day of the first quarter example: quarter = 1 and year is 2007. expected result is 01/01/2007 - 03/31/2007. similarly quarter = 2 and year is 2007 then expected result is 04/01/2007 - 06/20/2007. Thanks in advance.

<code> Hi, Tom I wrote the following procedure, there are two PLACEHOLDERs which means two bind variables and the two PLACEHOLERS have the same name, that is to say ,the name of PLACEHOLDERs are identical, they are both "MYTEST". In the procedure, I assign a value to the PLACEHOLDERs by DBMS_SQL.BIND_VARIABLE only once, but if I use 'EXECUTE IMMEDIATE' to execute a dynamic SQL, we have to separately assign value for the TWO PLACEHOLDERs even though their names are same. I'd like to know if the usage of "two PLACEHOLDERs with same name, bind only once" of DBMS_SQL is correct? I have done many experiments of this usage and I got the correct result. Thank a lot Best Regards create table test(id1 number,id2 number); insert into test values(1,2); insert into test values(2,1); insert into test values(2,3); commit; SQL> select * from test; ID1 ID2 ---------- ---------- 1 2 2 1 2 3 set serveroutput on declare v_sql varchar2(32767); v_cur pls_integer; v_execute pls_integer; v_column pls_integer; v_count pls_integer; V_ID pls_integer := 1; begin v_cur := dbms_sql.open_cursor; v_sql := 'select count(*) from (select * from test where id1=:MYTEST or id2=:MYTEST)'; dbms_sql.parse(v_cur,v_sql,dbms_sql.native); dbms_sql.bind_variable(v_cur,'MYTEST',V_ID); dbms_sql.define_column(v_cur,1,v_column); v_execute := dbms_sql.execute(v_cur); if dbms_sql.fetch_rows(v_cur) > 0 then dbms_sql.column_value(v_cur, 1, v_count); end if; dbms_sql.close_cursor(v_cur); dbms_output.put_line('count is '||to_char(v_count)); end; / count is 2 PL/SQL procedure successfully completed.</code>

This is a short history of our PFCLScan product and therefore also a history of the other apps now built on top of PFCLScan such as PFCLObfuscate , PFCLCode , PFCLForensics and more. Obviously after such a long time and....[Read More]

Posted by Pete On 15/05/25 At 10:15 AM

This year I can unfortunately only attend APEX Connect for 2 days, starting on Day 2 of the conference.
Conference is hosted in the famous EuropaPark.
The Day has started with the traditional 5K run (which ended up to be 5.8 km in fact) to wake-up the body with fresh air and nice sunshine.
After the “Welcome 2nd Day APEX Connect & Opening of the DB Conference, DOAG e.V.”, and the Keynote “Mehr über Oracle’s Release 23ai – immer noch ohne Folien & Marketing, dafür aber zu 100% Demos”, I decided to attend presentations on following topics:
– Fortgeschrittene API-Entwicklung mit Oracle REST Data Services (ORDS)
– Search Images by Images in your APEX application with AI Vector Search
– Sponsored Session: Revolutionizing Oracle APEX: United Codes’ Innovative Solutions
– Single Sign-On: One Login for All Your Needs
– Dev Talk: Die Trivadis PL/SQL & SQL Coding Guidelines sind tot – was nun?
And the day ended by the Evening Event: “Dinner at the french themed area” of the park followed by a party with a DJ.

Fortgeschrittene API-Entwicklung mit Oracle REST Data Services (ORDS)

REST APIs deserve careful design and looking into them from a consumer point of view can help a lot in this exercise. REST APIs have a grammar made of Nouns (preferred to be plural) to show what it is about, verbs given by the HTTP methods (GET, POST, PUT, DELETE) and relations to sub-resources.It is very important to try them, test them and document them properly with tools like Swagger/OpenAPI.
ORDS provides already a lot of help allowing to develop them, but sometimes custom PL/SQL is required which should be as light as possible due to the numerous calls they can get.
Authentication is another critical point which should be managed with JWT (JSON Web Token) on Pre-hook or oAuth2. This integrates well with APEX.

Search Images by Images in your APEX application with AI Vector Search

Oracle 23ai has introduced Vector search capabilities from Embedded data on LLM (Large Language Models). ” use cases were presented with LLMs running locally for privacy and data control, allowing to search images from text description and similar images.
This can be run within a Docker image containing following packages:
– ORDS
– Oracle 23ai DB
– Open WebUI
– Ollama (LLM)
– Apache tika (document extraction)
The embedding is managed with some Python code and APEX serves as Interface to the prompt which can be easily defined on a page as Dynamic Action declaratively.

Sponsored Session: Revolutionizing Oracle APEX: United Codes’ Innovative Solutions

“Ideas deserve to be realized” and “visioneering solutions to fit any requirements” are the 2 mantras of United Codes. The company is mostly known about AOP (APEX Office Print) which is natively supported by APEX. Beyond the success of that tool (more than 15’000 users) they provide much more like:
– APEX Office Edit
– APEX Message Service
– All sorts of APEX Plug-ins (Online PDF editor, Application Search, Drag &Drop, Tooltip, Splitter, as well as all FOEX free plug-ins they took over to support)
– APEX Project Eye
– dbLinter (still in development) for PL/SQL code check
And much more. Feel free to look at the provided links to learn more about those very useful tools.

Single Sign-On: One Login for All Your Needs

Single Sign On is becoming more and more a business request. It provides a single point of authentication.
APEX is supporting different protocols related to this topic, like oAuth2 and OpenID since version 18.1 and SAML since version 21.2. Thanks to the so called “Social Sign-in” and web credentials all kind of identity providers like okta, Keycloak, Oracle IAM, Azure ID are supported by APEX. This will replace the standard login by the one of the identity provider allowing to make use of their additional features like 2FA (2 Factor Authentication) which reduces drastically the hack attempts.
And the future tends to go toward passwordless authentication with tools like fingerprint of face recognition.

Dev Talk: Die Trivadis PL/SQL & SQL Coding Guidelines sind tot – was nun?

For year, Trivadis PL/SQL Guidelines have been seen as a reference for code development. But last version issued in March 2024 is the very last of it, as it no longer will be maintained.
As the need for quality code is more important than ever, there are alternatives to get the code checked against rules:
– SQLcl code scan
– PMD
– SonarQube
– ZPA
– SQLfluff
and a new joiner still in development: dbLinter which promises more flexibility, with more rules based on the Trivadis guidelines with integration into VSCode.
Beside that, SQL based tests are also a requirement which can be fulfilled by tools like:
– utPLSQL
– ora* CODECOP (which has an interface in APEX to manage the rules)
– QUASTO
and also dbLinter. Sounds like a very promising tool.

You can find a summary of Day 3 here.

L’article APEX Connect 2025 (Day 2) est apparu en premier sur dbi Blog.

It has been a long time coming but I have finally got this website running on HTTPS / SSL. Google have been pushing webmasters and site owners to move to HTTPS for more than 10 years. Google in fact stated....[Read More]

Posted by Pete On 13/05/25 At 02:07 PM

One of the goals of creating an interpreter written in PL/SQL to execute a custom language was for our use in our tools. We wanted to be able to ship PL/SQL and customise it after its deployed without re-compiling the....[Read More]

Posted by Pete On 14/04/25 At 12:11 PM

In the first blog in this series we discussed the main issue with using DBMS_CRYPTO to encrypt data within the database. This is the lack of key management provided by Oracle natively for use with this package. I had intended....[Read More]

Posted by Pete On 07/04/25 At 01:25 PM

I often get asked how to use DBMS_CRYPTO to encrypt data in the Oracle database. Or I used to be asked how to use DBMS_OBFUSCATION_TOOLKIT when it was the go-to encryption in an Oracle database. Before we go far; this....[Read More]

Posted by Pete On 02/04/25 At 02:22 PM

Just an update as I have not posted too many blogs recently. I have a bag log of blog ideas to write on technical subjects directly relating to Oracle security so please watch out for those by subscribing / following....[Read More]

Posted by Pete On 19/03/25 At 03:00 PM

Can we use AI in Oracle security? - yes as an answer? we can but how effective it would be means the answer is maybe? It depends on what we want to use AI for and how much data is....[Read More]

Posted by Pete On 27/02/25 At 03:35 PM